﻿create PROCEDURE Sync.SP_Projects_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Projects_Temp TABLE 
		(
			ProjectID UniqueIdentifier,
			[ProjectName] nvarchar(50),
				[ProfitPercent] decimal,
				[ParentID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Projects_Temp 
	   SELECT	Tbl.Col.value('ProjectID[1]','uniqueidentifier') ProjectID,
                Tbl.Col.value('ProjectName[1]', 'nvarchar(50)') [ProjectName],
Tbl.Col.value('ProfitPercent[1]', 'decimal') [ProfitPercent],
Tbl.Col.value('ParentID[1]', 'uniqueidentifier') [ParentID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Projects') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Projects] ([ProjectID],[ProjectName],[ProfitPercent],[ParentID])
	SELECT t.[ProjectID],t.[ProjectName],t.[ProfitPercent],t.[ParentID]
	FROM  @Projects_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Projects_Tracking s
        WHERE t.ProjectID = s.ProjectID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Projects]
	SET [Projects].[ProjectName] = t.[ProjectName],[Projects].[ProfitPercent] = t.[ProfitPercent],[Projects].[ParentID] = t.[ParentID] 
	FROM [dbo].[Projects] s JOIN @Projects_Temp t 
    ON t.ProjectID = s.ProjectID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Projects_Tracking r
				  WHERE t.ProjectID = r.ProjectID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Projects]
    FROM @Projects_Temp t JOIN [dbo].[Projects] s
    ON t.ProjectID = s.ProjectID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














